package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import rai.dto.ProjectDTO;
import rai.dto.ProjectEmpInfoDTO;
import rai.dto.RoleDTO;
import rai.form.EditTakenProject;
import rai.util.Constants;

/**
 * Database operation for EditTakenProject.
 *
 * @author Vu
 *
 */
public class EditTakenProjectDAO {
    /** Log property. */
    private static Logger log = Logger.getLogger("EditTakenPrjInfoDAO.start");
    public String addPrj(int empID, int roleID, int prjID){
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select * from projectexp "
                    + "where EmpID=" + empID + " and ProjectID=" + prjID;
            
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs=pStat.executeQuery(query);
            
            if(!rs.next()){
                query = "insert into projectexp "
                        + "value('" + prjID +"','" + empID +"','" + roleID + "',0x00)";
                conn = rai.util.Connection.getConnection();
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                
                
            } else {
                return "Data Exists in Database!!!";
            }
        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.addPrj" + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.addPrj " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.addPrj.end");
        return "Information Add Successfully!!!";
    }
    /**
     * Get List of Project.
     * @return list of project
     */
    public List<ProjectDTO> getPrj() {
        ProjectDTO dto;
        final List<ProjectDTO> result = new ArrayList<ProjectDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            query = "select distinct ProjectID,ProjectName from project";
            // get connection
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            // execute query
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                dto = new ProjectDTO();
                dto.setId(rs.getInt("ProjectID"));
                dto.setSname(rs.getString("ProjectName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getProject " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getProject " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Update Project Info.
     *
     * @param editform
     * @param EmpID
     */
    public void updatePrj(final EditTakenProject editform, final int EmpID) {
        log.info("EditTakenProject.UpdateProjectInfo");
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        // ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "update project p set p.PeriodFrom='" + editform.getStart()
                    + "',p.PeriodTo='" + editform.getEnd()
                    + "',p.Description='" + editform.getDes()
                    + "' where p.ProjectID=" + editform.getId();
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);
            query = "update projectexp pe set pe.Role='" + editform.getRole()
                    + "' where pe.ProjectID=" + editform.getId()
                    + " and pe.EmpID=" + EmpID;
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);

        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.getRole" + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.getRole " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.getRole.end");

    }

    /**
     * Get Role List.
     *
     * @return
     */
    public final List<RoleDTO> getRole() {
        final List<RoleDTO> result = new ArrayList<RoleDTO>();
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select * from role";
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();

            while (rs.next()) {
                final RoleDTO role = new RoleDTO();
                role.setId(rs.getInt("RoleID"));
                role.setSname(rs.getString("Role"));
                result.add(role);
            }
        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.getRole" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.getRole " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.getRole.end");
        return result;
    }

    /**
     * Get Project Info.
     * 
     * @param PrjID
     * @param empID
     * @return ProjectEmpInfoDTO
     */
    public ProjectEmpInfoDTO getProject(final int PrjID, final int empID) {
        log.debug("EditTakenProjectInfoDAO.getPrj.start");
        final ProjectEmpInfoDTO prj = new ProjectEmpInfoDTO();
        prj.setId(PrjID);
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            
            query = "select co.ComName,pe.Role,ProjectName,dp.DepartName \"DepartName\",PeriodFrom,"
            		+ "PeriodTo, p.Description, p.Size "
                    + "from project p,department dp, company co, projectexp pe "
                    + "where pe.ProjectID=p.ProjectID and p.DepartID=dp.DepartID"
                    + " and dp.CompanyID=co.CompanyID"
                    + " and p.ProjectID=" + PrjID
                    + " and pe.EmpID=" + empID;
            if (empID == 0) {
                query = "select co.ComName,ProjectName,dp.DepartName \"DepartName\",PeriodFrom,PeriodTo,"
                    + " p.Description, p.Size "
                    + "from project p,department dp, company co "
                    + "where p.DepartID=dp.DepartID and "
                    + "dp.CompanyID=co.CompanyID"
                    + " and p.ProjectID=" + PrjID;
                    
            }
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setSname(rs.getString("ProjectName"));
                prj.setStart(rs.getString("PeriodFrom"));
                prj.setEnd(rs.getString("PeriodTo"));
                prj.setDes(rs.getString("Description"));
                if (empID != 0) {
                    prj.setRole(rs.getString("Role"));
                }
                prj.setDept(rs.getString("DepartName"));
                prj.setCompany(rs.getString("ComName"));
                prj.setSize(rs.getInt("Size"));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Language\" "
                    + "from pits_db.language l, project_language pl "
                    + "where pl.LangID = l.LangID and pl.ProjectID ="+PrjID; 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setLang((rs.getString("Language")));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Database\" "
                + "from pits_db.database l, project_database pl "
                + "where pl.DatabaseID = l.DatabaseID and pl.ProjectID ="+PrjID; 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setDatabase((rs.getString("Database")));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                + "from pits_db.framework l, project_framework pl "
                + "where pl.FrameID = l.FrameID and pl.ProjectID ="+PrjID; 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setFrw((rs.getString("Framework")));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Framework\" "
                + "from pits_db.technology l, project_technology pl "
                + "where pl.TechID = l.TechID and pl.ProjectID ="+PrjID; 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setFrw(prj.getFrw()+","+(rs.getString("Framework")));
            }
            
            query = "select GROUP_CONCAT(l.Name) \"Application\" "
                + "from pits_db.application l, project_application pl "
                + "where pl.AppID = l.AppID and pl.ProjectID ="+PrjID; 
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs = pStat.executeQuery();
            while (rs.next()) {
                prj.setApp((rs.getString("Application")));
            }

        } catch (final Exception sqlEx) {
            log.error("EditTakenProjectDAO.getPrj" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditTakenProjectDAO.getPrj " + sqlEx);
            }
        }
        log.debug("EditTakenProjectDAO.getPrj.end");
        return prj;
    }
    
}
